package com.demo.utils;

import com.demo.beans.Bean;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

public class ExcelUtil {
    public static Workbook readExcel(String filePath){

        if(filePath == null || filePath.equals("")){
            return null;
        }
        //得到文件后缀
        String suffix = filePath.substring(filePath.lastIndexOf("."));
        System.out.println(suffix);
        try {
            InputStream is = new FileInputStream(filePath);
            if(".xls".equals(suffix)){
                System.out.println("文件类型是.xls");
                return new HSSFWorkbook(is);
            }
            if(".xlsx".equals(suffix)){
                System.out.println("文件类型是.xlsx");
                return new XSSFWorkbook(is);
            }
            return null;

        } catch (FileNotFoundException e) {
            e.printStackTrace();
            System.out.println("文件没有找到");
        } catch (IOException e) {
            e.printStackTrace();
            System.out.println("发生io异常");
        }
        return null;
    }

    public static Object getCellFormatValue(Cell cell){
        Object cellValue;
        if(cell!=null){
            //判断cell类型
            switch(cell.getCellType()){
                //空值单元格
                case Cell.CELL_TYPE_BLANK:{
//                    System.out.print("空单元格-");
                    cellValue = "";
                    break;
                }
                //数值型单元格 getNumericCellValue()以数字形式获取单元格的值。
                case Cell.CELL_TYPE_NUMERIC:{
                    //判断cell是否为日期格式
                    if(DateUtil.isCellDateFormatted(cell)){
//                        System.out.print("日期格式-");
                        //转换为日期格式YYYY-mm-dd
                        //cellValue = cell.getDateCellValue();
                        Date date = cell.getDateCellValue();
                        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                        cellValue = dateFormat.format(date);
                    }else{
                        //数字
//                        System.out.print("数字格式-");
                        cellValue = cell.getNumericCellValue();
                    }
                    break;
                }
                //公式型单元格getCellFormula()返回单元格的公式
                case Cell.CELL_TYPE_FORMULA:{
//                    System.out.println("公式型");
                    System.out.println(cell.getCellFormula());
                    cellValue = String.valueOf(cell.getCellFormula());
                    break;
                }
                //字符串单元格
                case Cell.CELL_TYPE_STRING:{
//                    System.out.print("字符串格式-");
                    cellValue = cell.getRichStringCellValue().getString();
                    break;
                }
                //布尔值型单元格
                case Cell.CELL_TYPE_BOOLEAN:{
//                    System.out.print("布尔值-");
                    cellValue = String.valueOf(cell.getBooleanCellValue());
                    break;
                }
                default:
                    cellValue = "";
            }
        }else{
            cellValue = "";
        }
        return cellValue;
    }


    /**
     * @author 文皓
     * @date 2022/6/24 9:26
     * @desc z
     *
     */
    public static HSSFWorkbook makeExcel(List<Bean> beans, String queryCondition){
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet(queryCondition);
        HSSFRow row = sheet.createRow(0);
        row.createCell(0).setCellValue("年份");
        row.createCell(1).setCellValue("批次代码");
        row.createCell(2).setCellValue("批次");
        row.createCell(3).setCellValue("院校代码");
        row.createCell(4).setCellValue("院校名称");
        row.createCell(5).setCellValue("科类代码");
        row.createCell(6).setCellValue("科类");
        row.createCell(7).setCellValue("专业名称");
        row.createCell(8).setCellValue("计划");
        row.createCell(9).setCellValue("投档最低分");
        row.createCell(10).setCellValue("最低分位次");
        HSSFRow rowCont = null;
        Bean bean = null;
        for (int i = 0; i < beans.size(); i++) {
            rowCont = sheet.createRow(i + 1);
            bean = beans.get(i);
            for (int j = 0; j <= 10; j++) {
                if (j == 0){
                    rowCont.createCell(j).setCellValue(bean.getYear());
                    continue;
                }
                if (j == 1){
                    rowCont.createCell(j).setCellValue(bean.getBatchCode());
                    continue;
                }if (j == 2){
                    rowCont.createCell(j).setCellValue(bean.getBatch());
                    continue;
                }if (j == 3){
                    rowCont.createCell(j).setCellValue(bean.getSchoolCode());
                    continue;
                }if (j == 4){
                    rowCont.createCell(j).setCellValue(bean.getSchoolName());
                    continue;
                }if (j == 5){
                    rowCont.createCell(j).setCellValue(bean.getBranchCode());
                    continue;
                }if (j == 6){
                    rowCont.createCell(j).setCellValue(bean.getBranch());
                    continue;
                }if (j == 7){
                    rowCont.createCell(j).setCellValue(bean.getMajor());
                    continue;
                }if (j == 8){
                    rowCont.createCell(j).setCellValue(bean.getPlanNum());
                    continue;
                }if (j == 9){
                    rowCont.createCell(j).setCellValue(bean.getLowestScore());
                    continue;
                }if (j == 10){
                    rowCont.createCell(j).setCellValue(bean.getZdfwc());
                    continue;
                }
            }
        }
        return workbook;
    }
}
